﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DEMO.Y2015CS58.LIB
{
    public class DataAccessDA
    {
        private static string conn = "";

        SqlConnection connect = new SqlConnection();

        public static void GetAppSetting(string Key)
        {
            conn = ConfigurationManager.AppSettings[Key];
        }

        private void GetConnectionString()
        {

            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            ConnectionStringsSection csSection = config.ConnectionStrings;

            conn = csSection.ConnectionStrings[2].ToString();
        }

        private void ChangeConnectionString(string connect)
        {

            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings[2].ConnectionString = connect;
            config.Save(ConfigurationSaveMode.Modified);

            ConfigurationManager.RefreshSection(config.ConnectionStrings.SectionInformation.Name);
        }

        public SqlConnection OpenConnect()
        {
            if (connect.State == ConnectionState.Closed || connect == null)
            {
                if (conn == "")
                    GetConnectionString();
                //GetAppSetting("kConnectionString");

                connect = new SqlConnection(conn);
                connect.Open();
            }

            return connect;
        }

        public void CloseConnect()
        {
            if (connect.State == ConnectionState.Open)
                connect.Close();

            if (connect.State == ConnectionState.Connecting)
                connect.Close();

            if (connect.State == ConnectionState.Executing)
                connect.Close();

            if (connect.State == ConnectionState.Fetching)
                connect.Close();

            if (connect.State == ConnectionState.Broken)
                connect.Close();

            connect.Dispose();

        }

        public bool TestConnection()
        {

            try
            {
                OpenConnect();

                if (connect.State == ConnectionState.Open)
                    return true;
                else
                    return false;

            }

            catch (Exception ex) { throw new Exception(ex.Message); }

            finally
            {
                CloseConnect();
            }

        }

        

        public DataTable GetDataTable(string query, SqlParameter[] param = null)
        {

            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = OpenConnect();

                SqlDataAdapter dapter = new SqlDataAdapter(cmd);

                DataTable result = new DataTable();
                dapter.Fill(result);

                return result;

            }

            catch (Exception ex) { throw new Exception(ex.Message); }

            finally
            {
                CloseConnect();
            }

        }

        public DataSet GetDataSet(string query, SqlParameter[] param = null)
        {

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = OpenConnect();

                SqlDataAdapter dapter = new SqlDataAdapter(cmd);

                DataSet result = new DataSet();

                dapter.Fill(result);

                return result;

            }

            catch (Exception ex) { throw new Exception(ex.Message); }

            finally
            {
                CloseConnect();
            }

        }

        public SqlDataReader ExecuteReader(string query, SqlParameter[] param = null)
        {

            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = OpenConnect();

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return reader;
            }

            catch (Exception ex) { throw new Exception(ex.Message); }

        }

        public object ExecuteScalar(string query, SqlParameter[] param = null)
        {

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = OpenConnect();

                return cmd.ExecuteScalar();

            }

            catch (Exception ex) { throw new Exception(ex.Message); }

            finally
            {
                CloseConnect();
            }

        }

        public int ExecuteNonQuery(string query, SqlParameter[] param = null)
        {

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = OpenConnect();

                return cmd.ExecuteNonQuery();

            }

            catch (Exception ex) { throw new Exception(ex.Message); }

            finally
            {
                CloseConnect();
            }

        }

        public int ExecuteNonQuery(string query, SqlConnection connect, SqlTransaction transaction, SqlParameter[] param = null)
        {

            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;

                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Connection = connect;

                transaction = connect.BeginTransaction(); //bắt đầu quá trình quản lý transaction
                cmd.Transaction = transaction;

                cmd.ExecuteNonQuery();

                transaction.Commit(); //cam kết thực hiện thành công

                return cmd.ExecuteNonQuery();

            }

            catch (Exception ex)
            {
                transaction.Rollback(); //quay lùi

                throw new Exception(ex.Message);
            }

            finally
            {
                CloseConnect();
            }

        }

        public object ExecuteNonQueryWithOutput(string query, SqlConnection connect, SqlTransaction transaction, string output, SqlParameter[] param = null)
        {

            try
            {

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;

                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Parameters[output].Direction = ParameterDirection.Output;
                cmd.Connection = connect;

                transaction = connect.BeginTransaction(); //bắt đầu quá trình quản lý transaction
                cmd.Transaction = transaction;

                cmd.ExecuteNonQuery();

                transaction.Commit(); //cam kết thực hiện thành công

                return cmd.Parameters[output].Value;

            }

            catch (Exception ex)
            {
                transaction.Rollback(); //quay lùi

                throw new Exception(ex.Message);
            }

            finally
            {
                CloseConnect();
            }
        }

        public object ExecuteNonQueryWithReturnvalue(string query, SqlConnection connect, SqlTransaction transaction, string returnValue, SqlParameter[] param = null)
        {

            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = query;
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.Parameters[returnValue].Direction = ParameterDirection.ReturnValue;
                cmd.Connection = connect;

                transaction = connect.BeginTransaction(); //bắt đầu quá trình quản lý transaction
                cmd.Transaction = transaction;

                cmd.ExecuteNonQuery();

                transaction.Commit(); //cam kết thực hiện thành công

                return cmd.Parameters[returnValue].Value;

            }

            catch (Exception ex)
            {
                transaction.Rollback(); //quay lùi

                throw new Exception(ex.Message);
            }

            finally
            {
                CloseConnect();
            }

        }

        public DataTable GetDataTableFromXMLFile(string path)
        {

            DataTable result = new DataTable();

            result.ReadXml(path);

            if (result == null)
                result = new DataTable();

            return result;

        }

    }

}
